Create New SQL Server Tables using SQL SELECT INTO

By: Koen Verbeeck   |   Updated: 2022-01-10   |   Comments (3)   |   Related: More > TSQL

With the SELECT INTO statement, you can quickly create a Microsoft SQL Server table using the result set of your SELECT statement. In this tutorial, we'll demonstrate some common scenarios with examples.

There are a couple of methods to create a new table in SQL Server. You can use the table designer of SQL Server Management Studio (SSMS) or you can write a CREATE TABLE statement using T-SQL. With the SELECT … INTO construct, we have a third option available. With some short examples, you'll discover how you can use this construct to quickly create new tables and copy data.

For the remainder of the tutorial, we assume you have the necessary permissions to actually create tables in a database.

The basic concept behind SELECT … INTO is that you create a new table using the result set of a SELECT statement. So, you can take any SELECT statement, add the INTO clause and you can create a new table and insert data into it at the same time! Let's illustrate using the Adventure Works sample database.

The following SELECT statements retrieves data from the existing table called Person:

To dump the data into a new table, we add the INTO clause to the statement, as well as a name for the new table as shown with the syntax below:

After executing the script, a new table will be created in the database:

In the messages window, you can see how many rows were inserted into this new table. When doing a straight SELECT from a table, the new table will have the same column names (when not using column aliases) and data types as the original table:

Even the NameStyle column is the same, which uses a user defined data type. If we use expressions in the SELECT statement, the data type will be determined on-the-fly based on the expressions used. For example, let's concatenate all the names of the Person table.

In the resulting table, we can see the FullName column is of type NVARCHAR(152).

The length of 152 is calculated by taking the 3 source columns – each of length 50 – together with the two spaces used in the concatenation.

You can limit the number of rows inserted in the new table. You can either use a TOP clause if it doesn't really matter which rows are inserted:

Or you can use the WHERE clause if you want to have more control over which rows are inserted. In the following example, we're only selecting rows of persons with a first name starting with the letter B.

Sometimes you need to create a new table to insert data into, but there are many columns and it would take some time to type it all out manually. If you have the SELECT statement that you're going to use later, you can use the INTO clause to quickly create your table. But what if the SELECT statement takes a very long time? By filtering out all of the data, you can still create the table without waiting! We can do this by adding a WHERE clause where the result is always false.

For example:

Now you have a table with no data, and you can script the CREATE TABLE statement using SSMS:

You now have the CREATE TABLE statement for your new table and you can make some adjustments, like setting the nullability of the columns, changing data types, setting a filegroup, configuring foreign and primary keys and so on. With this little trick, you can save yourself some time when creating tables.

The INTO clause is used in a SELECT statement, and this statement can be as complex as you want it to be. You can use joins, GROUP BY, UNION and so on. In fact, every example from the tip SQL Server SELECT Examples can be used. Just add INTO [TableName] right before the FROM clause. From that tip, we can for example use a query with an INNER JOIN, a WHERE clause, a GROUP BY and an ORDER BY clause:

In the previous examples, the created table was always in the same database as the source tables from the SELECT query. However, it's perfectly possible to have the destination table in another database if you specify a "three-part-name". This is database.schema.tablename.

Taking our very first example:

There are a couple of prerequisites:

Each time we've created a new "persisted" table in a database. But with the INTO clause you can also create temporary tables. These are tables stored in the tempdb database, and they are deleted once the connection is over.

Using our first example again, we can create a local temp table:

We can indeed find a new temporary table in tempdb:

Once the connection drops, the table will be deleted. A local temp table only exists for the connection that created it. Because it might be possible for multiple connections to create temp tables with the same name, a suffix is added to the local temp table in tempdb.

The full name of our temp table is actually:

A global temp table (which has two # before its name) is a temporary tables that can be shared between multiple connections. Let's create such a global temp table with INTO:

It's added again to the tempdb, but this time there's no suffix:

If you still have the query window open where you created the global temp table, you can create a new query window in SSMS and query the global temp table:

As long as there is at least one connection open that is referencing the global temp table, it will continue to exist. Once every connection is closed, it will be dropped as well.

Using INTO and temp tables is useful when you want to dump data into a table, but you only have use for it for a limited time. For example, when you are debugging a query, or in a stored procedure when you want to write intermediate results to disk. Once the stored proc is over, the data can be discarded.

Even though you can insert into a new temp table, you cannot use SELECT … INTO to create a table variable.

There are some drawbacks when you use SELECT … INTO to create a new table.